package com.sl.au.service.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.neo4j.cypher.internal.compiler.v2_2.perty.recipe.Pretty.page;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Direction;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;
import com.sl.au.service.OperateLogService;

import com.sl.au.entity.Account;
import com.sl.au.entity.User;
import com.sl.au.entity.ViewForEventRegister;
import com.sl.au.entity.ViewForUserScoreAnalysis;
import com.sl.au.entity.Evaluate;
import com.sl.au.entity.EventReport;
import com.sl.au.entity.BusinesssellerAuthentication;
import com.sl.au.entity.BusinesssellerBaseInfo;
import com.sl.au.entity.Option;
import com.sl.au.entity.PartBaseInfo;
import com.sl.au.repository.UserRepository;
import com.sl.au.repository.ViewForUserScoreAnalysisRepository;
import com.sl.au.repository.EvaluateRepository;
import com.sl.au.repository.BusinesssellerBaseInfoRepository;
import com.sl.au.repository.BusinesssellerAuthenticationRepository;
import com.sl.au.service.AccountService;
import com.sl.au.service.UserService;
import com.sl.au.service.OptionService;
import com.sl.au.service.ViewForUserScoreAnalysisService;
import com.sl.utils.ExdeUtil;

@Service("ScoreAnalysisService")
@Transactional
public class ViewForUserScoreAnalysisServiceImpl extends BaseServiceImpl implements ViewForUserScoreAnalysisService {
	
	@Autowired
	ViewForUserScoreAnalysisRepository viewForUserScoreAnalysisRepository;
	
	@Resource
	private JdbcTemplate jdbcTemplate;
	
	@Resource
	private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
	
	@Override
	public ViewForUserScoreAnalysisRepository getRepository() {
		return this.viewForUserScoreAnalysisRepository;
	}
	
	@Override
	public List<ViewForUserScoreAnalysis> getPage(int start, int length, String key, String orderName, String dir) {
		Page<ViewForUserScoreAnalysis> pages = null;
		
		Pageable pageable = new PageRequest(start / length, length, Sort.Direction.DESC, "createTime");
		if (StringUtils.hasText(key)) {
			pages = viewForUserScoreAnalysisRepository.getByKey(key,pageable);
		}else {
			pages = viewForUserScoreAnalysisRepository.findAll(pageable);
		}
		return pages.getContent();
	}
	
	@Override
	public int getCount(String key) {
		if (!StringUtils.hasText(key))
			return viewForUserScoreAnalysisRepository.findAll().size();
		Pageable pageable = new PageRequest(0, Integer.MAX_VALUE, Sort.Direction.DESC, "createTime");
		return viewForUserScoreAnalysisRepository.getByKey(key, pageable).getContent().size();
	}

	@Override
	public Map<String, Object> getAnalysisData(Date beginDate, Date endDate, String id) {
		Map<String, Object> resultMap = new HashMap<String, Object>();
		//参数设置
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("begin", beginDate);
		params.put("end", endDate);
		params.put("id", id);
		/*
		 * 根据评分层级分组分析数据
		 */
//		List<Map<String, Object>> gradingList = new ArrayList<Map<String, Object>>();
//		String GradingSQL = "SELECT g.gradingName as `name`, SUM(a.scale) as 'value' from sl_wcsi_EventToEvaluateUser a " + 
//				"LEFT join sl_dcms_grading g " + 
//				"on a.registerEventEvaluateLevel = g.id " + 
//				"WHERE a.createTime >:begin and a.createTime <:end and a.registerUser = :id "+
//				"GROUP BY g.gradingName";
//		gradingList = namedParameterJdbcTemplate.queryForList(GradingSQL,params);
//		this.setAnalysisData(resultMap, gradingList, "grading");
		
		/*
		 * 根据网格分组进行分析
		 */
		List<Map<String, Object>> gridList = new ArrayList<Map<String, Object>>();
		String GridSQL = "SELECT g.id AS id,	g.gridName AS `name`, d.divisionName as divsion, SUM(a.scale) AS `value` " + 
				" FROM" + 
				" sl_wcsi_EventToEvaluateUser a " + 
				" LEFT JOIN sl_wcsi_EventRegister e ON a.registerEvent = e.id " + 
				" LEFT JOIN sl_wcsi_gridArea g ON e.registerEventToNetGrid = g.id " + 
				" LEFT JOIN sl_dcms_division d ON g.areaId = d.id " + 
				" WHERE a.createTime >:begin and a.createTime <:end and a.registerUser = :id "+
				" GROUP BY g.id, g.gridName,d.divisionName ORDER BY d.createTime ";
		gridList = namedParameterJdbcTemplate.queryForList(GridSQL,params);
		this.setAnalysisData(resultMap, gridList, "grid");
		
		/*
		 * 根据区域分组进行分析
		 */
		List<Map<String, Object>> divisionList = new ArrayList<Map<String, Object>>();
		String DivsionSQL = "SELECT d.id AS id,	d.divisionName AS `name`, SUM(a.scale) AS `value` " + 
				" FROM" + 
				" sl_wcsi_EventToEvaluateUser a " + 
				" LEFT JOIN sl_wcsi_EventRegister e ON a.registerEvent = e.id " + 
				" LEFT JOIN sl_wcsi_gridArea g ON e.registerEventToNetGrid = g.id " + 
				" LEFT JOIN sl_dcms_division d ON g.areaId = d.id " + 
				" WHERE a.createTime >:begin and a.createTime <:end and a.registerUser = :id "+
				" GROUP BY d.id, d.divisionName ORDER BY d.createTime ";
		divisionList = namedParameterJdbcTemplate.queryForList(DivsionSQL,params);
		this.setAnalysisData(resultMap, divisionList, "division");

		/*
		 * 根据事件分组进行分析
		 */
		List<Map<String, Object>> eventList = new ArrayList<Map<String, Object>>();
		String EventSQL = "SELECT t.id AS id, t.eventName AS `name`,SUM(a.scale) AS `value` " + 
				" FROM sl_wcsi_EventToEvaluateUser a LEFT JOIN sl_wcsi_EventRegister e ON a.registerEvent = e.id " + 
				" LEFT JOIN sl_dcms_eventType t ON e.registerEventType = t.id " + 
				" WHERE a.createTime >:begin and a.createTime <:end and a.registerUser = :id "+
				" GROUP BY t.id,t.eventName";
		eventList = namedParameterJdbcTemplate.queryForList(EventSQL,params);
		this.setAnalysisData(resultMap, eventList, "event");
		
		/*
		 * 根据部件分组进行分析
		 */
		List<Map<String, Object>> partList = new ArrayList<Map<String, Object>>();
		String PartSQL = "SELECT p.id AS id, p.partName AS `name`, SUM(a.scale) AS `value` " + 
				" FROM sl_wcsi_EventToEvaluateUser a LEFT JOIN sl_wcsi_EventRegister e ON a.registerEvent = e.id " + 
				" LEFT JOIN sl_wcsi_PartBaseInfo p ON e.registerEventToPartBaseInfo = p.id " + 
				" WHERE a.createTime >:begin and a.createTime <:end and a.registerUser = :id "+
				" GROUP BY p.id,p.partName order by p.partName ";
		partList = namedParameterJdbcTemplate.queryForList(PartSQL,params);
		this.setAnalysisDataForPart(resultMap, partList, "part");

		return resultMap;
	}
	
	public void setAnalysisData(Map<String, Object> map,List<Map<String, Object>> list,String temp) {
		String[] legend = null;
		Object[] series = null;
		Map<String, Object> resultMap = new HashMap<String, Object>();
		if(list != null && list.size() >0) {
			legend = new String[list.size()];
			series = new Object[list.size()];
			int i=0;
			for(Map<String, Object> mapTemp : list) {
				String name = (String) mapTemp.get("name");
				legend[i] = name;
				series[i++] = mapTemp;
			}
		}else {
			legend = new String[0];
			series = new Object[0];
		}
		resultMap.put("legend", legend);
		resultMap.put("series", series);
		map.put(temp, resultMap);
	};
	
	
	public void setAnalysisDataForPart(Map<String, Object> map,List<Map<String, Object>> list,String temp) {
		String[] legend = new String[2];
		legend[0] = "部件";
		legend[1] = "非部件";
		
		Object[] seriesIn = new Object[2];
		seriesIn[0] = false;
		seriesIn[1] = false;
		Object[] seriesOut = null;

		double inPart = 0;
		double inNotPart = 0;
		Map<String, Object> part = new HashMap<String, Object>();
		Map<String, Object> notPart = new HashMap<String, Object>();
		Map<String, Object> resultMap = new HashMap<String, Object>();
		Map<String, Object> notPartTemp = new HashMap<String, Object>();
		if(list != null && list.size() >0) {
			seriesOut = new Object[list.size()];
			int i=0;
			for(Map<String, Object> mapTemp : list) {
				//判断部件 非部件
				String name = (String) mapTemp.get("name");
				if(name == null || name.equals("")) {
					inNotPart +=  ExdeUtil.str2double(String.valueOf(mapTemp.get("value")));
					mapTemp.put("name", "非部件");
					mapTemp.put("id", "notpart");
					notPartTemp = mapTemp;
					seriesIn[1] = true;
				}else{
					inPart +=  ExdeUtil.str2double(String.valueOf(mapTemp.get("value")));
					seriesOut[i++] = mapTemp;
					seriesIn[0] = true;
				}
			}
		}
		part.put("name", "部件");
		part.put("id", "part");
		part.put("value", inPart);
		notPart.put("name", "非部件");
		notPart.put("id", "notpart");
		notPart.put("value", inNotPart);
		if(seriesIn[1].equals(true)) {
			seriesOut[list.size()-1] = notPartTemp;
			seriesIn[1] = notPart;
		}
		if(seriesIn[0].equals(true)) {
			seriesIn[0] = part;
		}
		
		resultMap.put("legend", legend);
		resultMap.put("seriesIn", seriesIn);
		resultMap.put("seriesOut", seriesOut);
		
		map.put(temp, resultMap);
	};
	
	@Override
	public List<Map<String, Object>> getTowerUser(Date beginDate, Date endDate, String gridId, String gradingId) {
		//参数设置
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("begin", beginDate);
		params.put("end", endDate);
		params.put("gridId", gridId);
		params.put("gradingId", gradingId);
				
		/*
		 * 根据网格分组进行分析
		 */
		List<Map<String, Object>> userList = new ArrayList<Map<String, Object>>();
		String userSQL = "select a.`user` as `id`, u.`name` as `name`, IFNULL(sum(c.scale),0) as `value` " + 
				" from sl_wcsi_Grid_User_Details a " + 
				" left join sl_au_user u on u.id = a.`user` " + 
				" left join sl_wcsi_EventToEvaluateUser c on a.`user` = c.registerUser and c.createTime >:begin and c.createTime <:end  " + 
				" left join sl_wcsi_EventRegister d on c.registerEvent = d.id and d.registerEventToNetGrid = a.grid " + 
				" where a.grid = :gridId and a.grading = :gradingId and a.`user` is not null "+
				" group by a.`user` , u.`name` ";
		
		userList = namedParameterJdbcTemplate.queryForList(userSQL,params);
		
		return userList;
	}
	
	
	@Override
	public Map<String, Object> getEventAnalysisData(Date beginDate, Date endDate) {
		Map<String, Object> resultMap = new HashMap<String, Object>();
		//参数设置
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("begin", beginDate);
		params.put("end", endDate);
		
		/*
		 * 根据网格分组进行分析
		 */
		List<Map<String, Object>> eventTypeList = new ArrayList<Map<String, Object>>();
		String eventTypeSql = "select DISTINCT eventParent from viewforeventtype order by eventParent";
		eventTypeList = jdbcTemplate.queryForList(eventTypeSql);
		
		List<Map<String, Object>> gridList = new ArrayList<Map<String, Object>>();
		String GridSQL = "select m.*,ifnull(n.count,'0') as `count` from (" + 
				" select DISTINCT" + 
				"	IFNULL(gg.gridName,'其他') AS `grid`," + 
				"	tt.eventParent AS `event`" + 
				" from sl_wcsi_EventRegister aa" + 
				" LEFT JOIN sl_wcsi_gridArea gg ON aa.registerEventToNetGrid = gg.id" + 
				" LEFT JOIN (select distinct eventParent from viewforeventtype ) tt on 1=1" + 
				" where aa.registerEventAccTime >=:begin and aa.registerEventAccTime <=:end " +
				" ) m" + 
				" left join (" + 
				" SELECT" + 
				"	IFNULL(g.gridName,'其他') AS `grid`," + 
				"	t.eventParent AS `event`," + 
				"  	count(a.id) AS `count`" + 
				" FROM" + 
				"	sl_wcsi_EventRegister a" + 
				" LEFT JOIN sl_wcsi_gridArea g ON a.registerEventToNetGrid = g.id" + 
				" LEFT JOIN viewforeventtype t ON a.registerEventType = t.id" +
				 " where a.registerEventAccTime >=:begin and a.registerEventAccTime <=:end " + 
				" GROUP BY g.gridName, t.eventParent " + 
				" ) n on n.grid = m.grid and n.event = m.event" + 
				" GROUP BY m.grid, m.event";
		gridList = namedParameterJdbcTemplate.queryForList(GridSQL,params);
		this.setEventAnalysisData(resultMap, gridList, eventTypeList, "grid");

		/*
		 * 根据事件类别分组进行分析
		 */
		List<Map<String, Object>> eventList = new ArrayList<Map<String, Object>>();
		String EventSQL = "SELECT t.eventParent AS `name`, " + 
				" count(a.id) AS `value` " + 
				" FROM" + 
				" sl_wcsi_EventRegister a " + 
				" LEFT JOIN viewforeventtype t ON a.registerEventType = t.id " + 
				" where a.registerEventAccTime >=:begin and a.registerEventAccTime <=:end " +
				" GROUP BY t.eventParent ";
		eventList = namedParameterJdbcTemplate.queryForList(EventSQL,params);
		this.setEventAnalysisData(resultMap,eventList,"event");
		
		/*
		 * 根据时间类别分组进行分析
		 */		
		List<Map<String, Object>> dateList = new ArrayList<Map<String, Object>>();
		String dateSQL = "select m.*,ifnull(n.count,'0') as `count` from (" + 
				" select DISTINCT" + 
				"	DATE_FORMAT( aa.registerEventAccTime, \"%Y-%m-%d\" ) as `accdate`," + 
				"	tt.eventParent AS `event`" + 
				" from sl_wcsi_EventRegister aa" + 
				" LEFT JOIN (select distinct eventParent from viewforeventtype ) tt on 1=1" + 
				" where aa.registerEventAccTime >=:begin and aa.registerEventAccTime <=:end " +
				" ) m" + 
				" left join (" + 
				" SELECT" + 
				"	DATE_FORMAT( a.registerEventAccTime, \"%Y-%m-%d\" ) as `accdate`," + 
				"	t.eventParent AS `event`," + 
				"  	count(a.id) AS `count`" + 
				" FROM" + 
				"	sl_wcsi_EventRegister a" + 
				" LEFT JOIN viewforeventtype t ON a.registerEventType = t.id" +
				 " where a.registerEventAccTime >=:begin and a.registerEventAccTime <=:end " + 
				" GROUP BY DATE_FORMAT( a.registerEventAccTime, \"%Y-%m-%d\" ), t.eventParent " + 
				" ) n on n.accdate = m.accdate and n.event = m.event" + 
				" GROUP BY m.accdate, m.event";
		dateList = namedParameterJdbcTemplate.queryForList(dateSQL,params);
		this.setEventAnalysisDataByDate(resultMap, dateList, eventTypeList, "date");
		

		/*
		 * 根据事件类别分组进行分析
		 */
		List<Map<String, Object>> statusList = new ArrayList<Map<String, Object>>();
		String statusSQL = "SELECT t.sTitle AS `name`, " + 
				" count(a.id) AS `value` " + 
				" FROM" + 
				" sl_wcsi_EventRegister a " + 
				" LEFT JOIN sl_dcms_option t ON a.registerEventState = t.id " + 
				" where a.registerEventAccTime >=:begin and a.registerEventAccTime <=:end " +
				" GROUP BY t.sTitle ";
		statusList = namedParameterJdbcTemplate.queryForList(statusSQL,params);
		this.setEventAnalysisData(resultMap,statusList,"status");

		return resultMap;
	}
	
	public void setEventAnalysisData(Map<String, Object> map,List<Map<String, Object>> list,List<Map<String, Object>> eventTypeList,String temp) {
		String[] legend = null;
		Object[] series = null;
		Object[] xAxis = null;
		legend = new String[eventTypeList.size()];
		series = new Object[eventTypeList.size()];
		xAxis = new String[list.size() / 3];
		for(int i=0;i<eventTypeList.size() ; i++) {
			Map<String, Object> tempMap = eventTypeList.get(i);
			String name = (String) tempMap.get("eventParent");
			legend[i] = name;
			Map<String, Object> seriesMap = new HashMap<String,Object>();
			seriesMap.put("name", name);
			seriesMap.put("type", "bar");
			seriesMap.put("stack", "group");
			String[] data = new String[list.size() / 3];
			seriesMap.put("data", data);
			series[i] = seriesMap;
		}
		Map<String, Object> resultMap = new HashMap<String, Object>();
		
		if(list != null && list.size() >0) {
			for(int j=0;j<list.size();j++) {
				Map<String, Object> mapTemp = list.get(j);
				String count = (String) mapTemp.get("count");
				int tempNum = j % 3;
				if(tempNum == 0) {
					String grid = (String) mapTemp.get("grid");
					xAxis[j/3] = grid;
				}
				int num = (int) Math.floor(j / 3);
				
				Map<String, Object> tempMap = (Map<String, Object>) series[tempNum];
				String[] str = (String[]) tempMap.get("data");
				str[num] = count;
				tempMap.put("data", str);
				series[tempNum] = tempMap;	
			}
		}
		resultMap.put("legend", legend);
		resultMap.put("series", series);
		resultMap.put("xAxis", xAxis);
		map.put(temp, resultMap);
	};
	
	public void setEventAnalysisData(Map<String, Object> map,List<Map<String, Object>> list,String temp) {
		String[] legend = null;
		Object[] series = null;
		Map<String, Object> resultMap = new HashMap<String, Object>();
		if(list != null && list.size() >0) {
			legend = new String[list.size()];
			series = new Object[list.size()];
			int i=0;
			for(Map<String, Object> mapTemp : list) {
				String name = (String) mapTemp.get("name");
				legend[i] = name;
				series[i++] = mapTemp;
			}
		}else {
			legend = new String[0];
			series = new Object[0];
		}
		resultMap.put("legend", legend);
		resultMap.put("series", series);
		map.put(temp, resultMap);
	};
	
	public void setEventAnalysisDataByDate(Map<String, Object> map,List<Map<String, Object>> list,List<Map<String, Object>> eventTypeList,String temp) {
		String[] legend = null;
		Object[] series = null;
		Object[] xAxis = null;
		legend = new String[eventTypeList.size()];
		series = new Object[eventTypeList.size()];
		xAxis = new String[list.size() / 3];
		for(int i=0;i<eventTypeList.size() ; i++) {
			Map<String, Object> tempMap = eventTypeList.get(i);
			String name = (String) tempMap.get("eventParent");
			legend[i] = name;
			Map<String, Object> seriesMap = new HashMap<String,Object>();
			seriesMap.put("name", name);
			seriesMap.put("type", "bar");
			seriesMap.put("stack", "group");
			String[] data = new String[list.size() / 3];
			seriesMap.put("data", data);
			series[i] = seriesMap;
		}
		Map<String, Object> resultMap = new HashMap<String, Object>();
		
		if(list != null && list.size() >0) {
			for(int j=0;j<list.size();j++) {
				Map<String, Object> mapTemp = list.get(j);
				String count = (String) mapTemp.get("count");
				int tempNum = j % 3;
				if(tempNum == 0) {
					String grid = (String) mapTemp.get("accdate");
					xAxis[j/3] = grid;
				}
				int num = (int) Math.floor(j / 3);
				
				Map<String, Object> tempMap = (Map<String, Object>) series[tempNum];
				String[] str = (String[]) tempMap.get("data");
				str[num] = count;
				tempMap.put("data", str);
				series[tempNum] = tempMap;	
			}
		}
		resultMap.put("legend", legend);
		resultMap.put("series", series);
		resultMap.put("xAxis", xAxis);
		map.put(temp, resultMap);
	};
	
}
